<?php
/**
 * Created by JetBrains PhpStorm.
 * User: Evan
 * Date: 12-11-8
 * Time: 下午11:06
 * To change this template use File | Settings | File Templates.
 */
class Model_Cfg_SearchItem extends Jwawa_BaseModel
{
    protected static $validators = array(
        'id' => array(
            'rules' => array('Int' => null),
            'require' => true,
            'for' => array('update')
        ),
        'page_id' => array(
            'rules' =>  array('Int' => null),
            'require' => true
        ),
        'entity_detail_id' => array(
            'rules' =>  array('Int' => null),
            'require' => true
        ),
        'item_type' => array(
            'rules' => array(
                'StringLength' => array('max' => 100)
            ),
            'require' => true
        ),
        'item_class' => array(
            'rules' => array(
                'StringLength' => array('max' => 100)
            ),
            'require' => true
        ),
        'seq' => array(
            'rules' =>  array('Int' => null),
            'require' => true,
            'for' => array('create')
        ),
        'item_cfg' => array(
            'rules' => array(
                'StringLength' => array('max' => 2000)
            )
        ),
        'condition_flag' => array(
            'rules' => array(
                'StringLength' => array('max' => 10)
            ),
            'require' => true
        ),
        'create_by' => array(
            'rules' => array(
                'StringLength' => array('min' => 1,'max' => 10)
            ),
            'require' => true,
            'for' => array('create')
        ),
        'update_by' => array(
            'rules' => array(
                'StringLength' => array('min' => 1,'max' => 10)
            ),
            'require' => true
        ),
        'create_time' => array(
            'rules' => array(
                'Date' => array(
                    'format' => 'yyyy-MM-dd HH:mm:ss'
                )
            ),
            'require' => true,
            'for' => array('create')
        ),
        'update_time' => array(
            'rules' => array(
                'Date' => array(
                    'format' => 'yyyy-MM-dd HH:mm:ss'
                )
            ),
            'require' => true
        ),
        'is_deleted' =>  array(
            'rules' => array(
                'InArray' => array('Y', 'N')
            ),
            'require' => true
        ),
    );

    function __construct(&$prop_array = null)
    {
        parent::__construct($prop_array);

        if($this->entity_detail_id === 0){
            $this->entity_detail_id = null;
        }
    }

    public static function saveOrUpdate($searchItem){
        if(empty($searchItem->id)){
            $searchItem = self::save($searchItem);
        } else {
            $searchItem = self::update($searchItem);
        }

        return $searchItem;
    }

    public function save($searchItem){
        // gen seq
        $sql = "SELECT max(seq) mx FROM cfg_search_items
                WHERE is_deleted = 'N'
                AND page_id = :page_id";
        $stmt = self::createDefaultStmt(self::getDb(), $sql);
        $stmt->setProperties($searchItem);
        $stmt->execute();

        if($rst = $stmt->fetchObj()){
            $searchItem->seq = $rst->mx + 1;
        }else{
            $searchItem->seq = 1;
        };

        Jwawa_Validate_Factory::validate(self::$validators, $searchItem);
        self::validateUniqueItemEntityDetailId($searchItem);

        // insert
        $sql = 'INSERT INTO cfg_search_items (page_id, entity_detail_id, item_type, item_class, seq, item_cfg, condition_flag, create_time, update_time, create_by, update_by, is_deleted)
                VALUES (:page_id, :entity_detail_id, :item_type, :item_class, :seq, :item_cfg, :condition_flag, :create_time, :update_time, :create_by, :update_by, :is_deleted)';
        $stmt = self::createDefaultStmt(self::getDb(), $sql);
        $stmt->setProperties($searchItem);

        if($rst = $stmt->execute()){
            $id = self::getDb()->lastInsertId();
            $searchItem->id = $id;
            return $searchItem;
        } else {
            return null;
        }
    }

    public function update($searchItem){
        Jwawa_Validate_Factory::validate(self::$validators, $searchItem, 'update');
        self::validateUniqueItemEntityDetailId($searchItem);

        $sql = "UPDATE cfg_search_items
                SET page_id = :page_id,
                    entity_detail_id = :entity_detail_id,
                    item_type = :item_type,
                    item_class = :item_class,
                    item_cfg = :item_cfg,
                    condition_flag = :condition_flag,
                    create_time = :create_time,
                    create_by = :create_by,
                    update_time = :update_time,
                    update_by = :update_by,
                    is_deleted = 'N'
                WHERE id = :id";
        $stmt = self::createDefaultStmt(self::getDb(), $sql);
        $stmt->setProperties($searchItem);
        $stmt->execute();

        return $searchItem;
    }

    private static function validateUniqueItemEntityDetailId($searchItem){
        $sql = "SELECT count(*) c FROM cfg_search_items
                WHERE is_deleted = 'N' AND entity_detail_id = :entity_detail_id  AND page_id = :page_id ";
        if($searchItem->id){
            $sql .= ' AND id != :id';
        }
        $stmt = self::createDefaultStmt(self::getDb(), $sql);
        $stmt->setProperties($searchItem);
        $stmt->execute();
        $rst = $stmt->fetchObj();

        if($rst->c != 0){
            throw new Jwawa_BusinessException("ITEM_ENTITY_DETAIL_ID_NOT_UNIQUE");
        }
    }

    public static function reduceleaterItemSeq($pageId, $current_seq){
        $dsql = "UPDATE cfg_search_items
                 SET seq = seq - 1
                 WHERE seq >= :current_seq + 1
                   AND page_id = :page_id
                   AND is_deleted = 'N'";
        $stmt = self::createDefaultStmt(self::getDb(), $dsql);
        $stmt->bindValue('current_seq', $current_seq);
        $stmt->bindValue('page_id', $pageId);
        $stmt->execute();
    }

    public static function changeSeqToBottom($id){
        $searchItem = self::getItemById($id);

        $mxsql ="SELECT max(seq) mx
                FROM cfg_search_items
                WHERE page_id = :page_id
                   AND is_deleted = 'N'";
        $stmt = self::createDefaultStmt(self::getDb(), $mxsql);
        $stmt->bindValue('page_id', $searchItem->page_id);
        $stmt->execute();
        $rst = $stmt->fetchObj();

        self::reduceleaterItemSeq($searchItem->page_id, $searchItem->seq);

        $usql = "UPDATE cfg_search_items
                 SET seq = :max_seq
                 WHERE id = :id";
        $stmt = self::createDefaultStmt(self::getDb(), $usql);
        $stmt->bindValue('max_seq', $rst->mx);
        $stmt->bindValue('id', $searchItem->id);
        $stmt->execute();
    }

    public static function changeSeqToDown($id){
        $searchItem = self::getItemById($id);
        $dsql = "UPDATE cfg_search_items
                 SET seq = seq - 1
                 WHERE seq = :current_seq + 1
                   AND page_id = :page_id
                   AND is_deleted = 'N'";
        $stmt = self::createDefaultStmt(self::getDb(), $dsql);
        $stmt->bindValue('current_seq', $searchItem->seq);
        $stmt->bindValue('page_id', $searchItem->page_id);
        $stmt->execute();

        $usql = "UPDATE cfg_search_items
                 SET seq = seq + 1
                 WHERE id = :id";
        $stmt = self::createDefaultStmt(self::getDb(), $usql);
        $stmt->bindValue('id', $searchItem->id);
        $stmt->execute();
    }

    public static function changeSeqToUp($id){
        $searchItem = self::getItemById($id);
        $dsql = "UPDATE cfg_search_items
                 SET seq = seq + 1
                 WHERE seq = :current_seq - 1
                   AND page_id = :page_id
                   AND is_deleted = 'N'";
        $stmt = self::createDefaultStmt(self::getDb(), $dsql);
        $stmt->bindValue('current_seq', $searchItem->seq);
        $stmt->bindValue('page_id', $searchItem->page_id);
        $stmt->execute();

        $usql = "UPDATE cfg_search_items
                 SET seq = seq - 1
                 WHERE id = :id";
        $stmt = self::createDefaultStmt(self::getDb(), $usql);
        $stmt->bindValue('id', $searchItem->id);
        $stmt->execute();
    }

    public static function changeSeqToTop($id){
        $searchItem = self::getItemById($id);
        $dsql = "UPDATE cfg_search_items
                 SET seq = seq + 1
                 WHERE seq <= :current_seq - 1
                   AND page_id = :page_id
                   AND is_deleted = 'N'";
        $stmt = self::createDefaultStmt(self::getDb(), $dsql);
        $stmt->bindValue('current_seq', $searchItem->seq);
        $stmt->bindValue('page_id', $searchItem->page_id);
        $stmt->execute();

        $usql = "UPDATE cfg_search_items
                 SET seq = 1
                 WHERE id = :id";
        $stmt = self::createDefaultStmt(self::getDb(), $usql);
        $stmt->bindValue('id', $searchItem->id);
        $stmt->execute();
    }

    public static function getItemById($id){
        $sql = "SELECT csi.id,
                        csi.entity_detail_id,
                        csi.item_type,
                        csi.item_class,
                        csi.seq,
                        csi.condition_flag,
                        csi.item_cfg,
                        csi.page_id
                    FROM cfg_search_items csi
                    WHERE csi.id = :id
                      and csi.is_deleted = 'N'";
        $stmt = self::createDefaultStmt(self::getDb(), $sql);
        $stmt->bindValue('id', $id);
        $stmt->execute();
        if($rst = $stmt->fetchObj('Model_Cfg_SearchItem')){
            return $rst;
        }else{
            throw new Jwawa_BusinessException('NOT_FOUND_OBJECT_WITH_ID', [get_class(), $id]);
        }
    }

    public static function getFirstItemIdByPageId($pageId){
        $sql = "SELECT csi.id
                    FROM cfg_search_items csi
                    WHERE csi.page_id = :page_id
                      and csi.is_deleted = 'N'
                    ORDER BY csi.seq
                    LIMIT 0,1";
        $stmt = self::createDefaultStmt(self::getDb(), $sql);
        $stmt->bindValue('page_id', $pageId);
        $stmt->execute();
        if($rst = $stmt->fetchObj()){
            return $rst->id;
        }else{
            return null;
        }
    }

    public static function findSearchItemByPgCodeWithNameLimit($pageCode, &$searchItemNameArray){
        $inSearchItems = '';
        foreach($searchItemNameArray as $searchItemName){
            $inSearchItems .= "'" . $searchItemName . "',";
        }

        $inSearchItems = rtrim($inSearchItems, ',');

        $sql = "SELECT ced.entity_field_name, csi.condition_flag, csi.item_type, ced.sql_field
                FROM cfg_search_items csi
                JOIN cfg_entity_details ced on ced.id = csi.entity_detail_id
                JOIN cfg_search_pages csp on csp.id = csi.page_id
                WHERE csp.page_code = :page_code
                AND csi.is_deleted = 'N' ";

        if(!empty($searchItemNameArray) && count($searchItemNameArray) > 0){
            $sql .= " AND ced.entity_field_name in ($inSearchItems)";
        }

        $stmt = self::createDefaultStmt(self::getDb(), $sql);
        $stmt->bindValue('page_code', $pageCode);
        $stmt->execute();
        $rst = $stmt->fetchAllObj();

        return $rst;
    }

    public static function findSearchItemByPageId($pageId){
        $sql = "SELECT csi.id,
                        csi.entity_detail_id,
                        ced.entity_field_name,
                        ced.entity_field_label,
                        csi.item_type,
                        csi.item_class,
                        csi.seq,
                        csi.condition_flag,
                        csi.item_cfg,
                        csi.page_id
                FROM cfg_search_items csi
                JOIN cfg_search_pages csp on csp.id = csi.page_id
                JOIN cfg_entity_details ced on ced.id = csi.entity_detail_id
                WHERE csi.page_id = :page_id
                AND csi.is_deleted = 'N'
                ORDER BY csi.seq";

        $stmt = self::createDefaultStmt(self::getDb(), $sql);
        $stmt->bindValue('page_id', $pageId);
        $stmt->execute();
        $rst = $stmt->fetchAllObj('Model_Cfg_SearchItem');

        return $rst;
    }

    public static function findSearchItemByPageCode($pageCode){
        $sql = "SELECT csi.id,
                        csi.entity_detail_id,
                        ced.entity_field_name,
                        ced.entity_field_label,
                        csi.item_type,
                        csi.item_class,
                        csi.seq,
                        csi.condition_flag,
                        csi.item_cfg,
                        csi.page_id
                FROM cfg_search_items csi
                JOIN cfg_search_pages csp on csp.id = csi.page_id
                JOIN cfg_entity_details ced on ced.id = csi.entity_detail_id
                WHERE csp.page_code = :page_code
                AND csi.is_deleted = 'N'
                ORDER BY csi.seq";

        $stmt = self::createDefaultStmt(self::getDb(), $sql);
        $stmt->bindValue('page_code', $pageCode);
        $stmt->execute();
        $rst = $stmt->fetchAllObj('Model_Cfg_SearchItem');

        return $rst;
    }

    public static function getSearchItemById($id){
        $sql = "SELECT csi.id,
                        csi.entity_detail_id,
                        csi.item_type,
                        csi.item_class,
                        csi.seq,
                        csi.condition_flag,
                        csi.item_cfg,
                        csi.page_id,
                        csp.page_name,
                        ced.entity_field_name,
                        ced.entity_field_label
                FROM cfg_search_items csi
                JOIN cfg_search_pages csp on csp.id = csi.page_id
                JOIN cfg_entity_details ced on ced.id = csi.entity_detail_id
                WHERE csi.id = :id
                AND csi.is_deleted = 'N'";

        $stmt = self::createDefaultStmt(self::getDb(), $sql);
        $stmt->bindValue('id', $id);
        $stmt->execute();
        $rst = $stmt->fetchObj('Model_Cfg_SearchItem');

        if(!$rst){
            throw new Jwawa_BusinessException('NOT_FOUND_OBJECT_WITH_ID', [get_class(), $id]);
        }

        return $rst;
    }

    public static function findSearchItemByGrpId($searchGroupId){
        $sql = "select csi.id,
                  csi.page_id,
                  csi.item_class,
                  csi.entity_detail_id,
                  csi.item_type,
                  csi.item_cfg,
                  csgis.default_value,
                  csgis.seq
                from cfg_search_groups csg
                join cfg_search_grp_itm_ships csgis on csgis.search_group_id = csg.id
                join cfg_search_items csi on csi.id = csgis.search_item_id
                where csg.id = :search_group_id
                  and csi.is_deleted = 'N'
                  order by csgis.seq";

        $stmt = self::createDefaultStmt(self::getDb(), $sql);
        $stmt->bindValue('search_group_id', $searchGroupId);
        $stmt->execute();
        $rst = $stmt->fetchAllObj('Model_Cfg_SearchItem');

        return $rst;
    }

    public static function delSearchItemById($id){
        if(!$id){
            throw new Jwawa_BusinessException("OBJECT_ID_INVALID", "SearchItem");
        }

        $searchItem = self::getSearchItemById($id);
        $sql = "UPDATE cfg_search_items
                SET seq = seq - 1
                WHERE seq > :seq
                AND page_id = :page_id
                AND is_deleted = 'N'";
        $stmt = self::createDefaultStmt(self::getDb(), $sql);
        $stmt->bindValue('seq', $searchItem->seq);
        $stmt->bindValue('page_id', $searchItem->page_id);
        $stmt->execute();

        $sql = "UPDATE cfg_search_items
                SET is_deleted = 'Y'
                WHERE id = :id";
        $stmt = self::createDefaultStmt(self::getDb(), $sql);
        $stmt->bindValue('id', $id);
        $stmt->execute();
    }

    public static function delSearchItemByPageId($pageId){
        if(!$pageId){
            throw new Jwawa_BusinessException("OBJECT_ID_INVALID", "SearchPage");
        }

        $sql = "UPDATE cfg_search_items
                SET is_deleted = 'Y'
                WHERE page_id = :page_id
                AND is_deleted = 'N'";
        $stmt = self::createDefaultStmt(self::getDb(), $sql);
        $stmt->bindValue('page_id', $pageId);
        $stmt->execute();
    }
}